<html>
    <title>STOCK Reports</title>
    <body>

        <%@ page errorPage="error.jsp" %>
        <%@ page import="net.sf.jasperreports.engine.*" %>
        <%@ page import="com.myapp.struts.reports.*" %>
        <%@ page import="net.sf.jasperreports.engine.util.*" %>
        <%@ page import="net.sf.jasperreports.engine.export.*" %>
        <%@ page import="net.sf.jasperreports.j2ee.servlets.*" %>
        <%@ page import="net.sf.jasperreports.engine.util.JRProperties" %>
        <%@ page import="java.util.*" %>
        <%@ page import="java.io.*" %>
        <%@ page import="java.sql.* , net.sf.jasperreports.engine.base.*,com.myapp.struts.dao.*,java.math.*"%>
        <%@ page import="com.lowagie.text.*"%>
        <%@ page import="com.lowagie.text.pdf.PdfWriter"%>
        <%@ page import="com.lowagie.text.pdf.BaseFont"%>
        <%@ page import="com.lowagie.text.pdf.PdfContentByte"%>
        <%@ page import="java.sql.* , net.sf.jasperreports.engine.base.*,com.jipl.dao.*,java.math.BigDecimal"%>

        <%
	
                File reportFile = new File(application.getRealPath("jrxml/Totalitemsused.jasper"));
                Vector v1= new Vector();
                Vector v2= new Vector();
                Vector v3= new Vector();
                Vector v4= new Vector();
                Vector v5= new Vector();
                Vector v6= new Vector();
                Vector v7= new Vector();
                Vector v8= new Vector();
	
                request.getSession(true);
                String un=session.getAttribute("name").toString();
                String locs=session.getAttribute("sitename").toString();
                String rd=session.getAttribute("date").toString();
	
	
            if (!reportFile.exists())
                        throw new JRRuntimeException("File Totalitemsused.jrxml not found. The report design must be compiled first.");
                JasperReport jasperReport = (JasperReport)JRLoader.loadObject(reportFile.getPath());
                Map parameters = new HashMap();
                parameters.put("ReportTitle", "Address Report");
                parameters.put("BaseDir", reportFile.getParentFile());

                ResultSet res=null,r1=null,r2=null,r3=null,r4=null;
                int data=0;
                String loc="";
                Connection con=null;
                Connect db=new Connect();
                con=db.mySql();
                if (con!=null)
                {
                                String sqa="select count(distinct(item)) from stock,items WHERE " +
                            "items.itemname=stock.item and stock.siteCode='"+locs+"' and sdate<='"+rd+"'";
                                        System.out.println("sqa=====------"+sqa);
                         PreparedStatement pstmt1=con.prepareStatement(sqa);
                            r1 = pstmt1.executeQuery();
                                                if (r1.next()==true)
                                                {
                                                data=(int) r1.getInt(1);
                            System.out.println("data"+data);
                                                }
	
                //----------------------------------------------
                String sql1="";
			
                        sql1="SELECT stock.item,stock.siteCode,sum(stock.pqty),sum(stock.rejected),sum(stock.alloc),sum(stock.value)" +
                     ",items.uom FROM stock,items WHERE items.itemname=stock.item and stock.siteCode='"
                        +locs+"' and sdate <= '"+rd+"' group by item";
                System.out.println(sql1);
		
                PreparedStatement pstmt5=con.prepareStatement(sql1);
                        res = pstmt5.executeQuery();
                        String ena="",location="",ena1="",sn="",lo="";
                        BigDecimal pqty,client1,rate1,client,aqty,rqty;
                        java.util.Date ncdate;
                        double rate=0.0,value1=0.0;
                        while (res.next()==true)
                        {
                    System.out.println("-----------");
                                ena= (String) res.getString(1);
                                lo=(String) res.getString(2);
                                pqty=  (BigDecimal) res.getBigDecimal(3);
                                rqty=  (BigDecimal) res.getBigDecimal(4);
            
                                 v1.add(ena);
                                 v2.add(lo);
                                 v3.add(pqty);
                                 v4.add(rqty);
                                 v5.add(res.getBigDecimal(5));
                                 v6.add(res.getBigDecimal(6));
                     v7.add(res.getString(7));
                        }
                //----------------------------------------------
                JasperPrint jasperPrint
                =JasperFillManager.fillReport(jasperReport,parameters,new OriginalStockReport(data,v1,v2,v3,v4,v5,v6,v7,rd));
                JRPdfExporter exporter = new JRPdfExporter();
                StringBuffer sbuffer = new StringBuffer();
                File destFile = new File(reportFile.getParent(), jasperPrint.getName() + ".pdf");
                exporter.setParameter(JRExporterParameter.JASPER_PRINT, jasperPrint);
                exporter.setParameter(JRExporterParameter.OUTPUT_FILE, destFile);
                exporter.setParameter(JRPdfExporterParameter.IS_ENCRYPTED, Boolean.TRUE);
                exporter.setParameter(JRPdfExporterParameter.IS_128_BIT_KEY, Boolean.TRUE);
                exporter.setParameter(JRPdfExporterParameter.IS_COMPRESSED , Boolean.TRUE);
            exporter.setParameter(JRPdfExporterParameter.PERMISSIONS, new Integer(PdfWriter.AllowCopy | PdfWriter.AllowPrinting));
                exporter.exportReport();
                con.close();
                }
        %>

        <div align="center">
            <p>&nbsp;</p>
            <p>&nbsp;</p>
            <p>&nbsp;</p>
            <p>&nbsp;</p>
            <h2 align="center" class="style1">
            <jsp:forward page="../../../../jrxml/Totalitemsused.pdf"/></h2>
        </div>
    </body>
</html>